package com.ekgc.addressbook.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ekgc.addressbook.common.Contacts;
import org.apache.ibatis.annotations.*;

@Mapper
public interface ContactMapper extends BaseMapper<Contacts> {

    /**
     * 带分组查询的分页查询（注解版）
     * @param page 分页参数（MyBatis-Plus自动填充）
     * @param keyword 搜索关键词
     * @param phoneNumber 用户手机号（关联用户ID）
     * @return 分页结果（含group_name映射）
     */
    @Select({
            "<script>",
            "SELECT",
            "c.id,",
            "c.user_id AS userId,",
            "c.name,",
            "c.phone_number AS phoneNumber,",
            "c.gender,",
            "c.birth_date AS birthDate,",
            "c.email,",
            "c.address,",
            "c.create_time AS createTime,",
            "c.update_time AS updateTime,",
            "ug.group_name AS usergroups",
            "FROM contacts c",
            "JOIN (",
            "  SELECT ui.id",
            "  FROM user_info ui",
            "  WHERE ui.phone_number = #{phoneNumber}",
            ") ui ON c.user_id = ui.id",
            "LEFT JOIN contact_group_relation cgr ON c.id = cgr.contact_id",
            "LEFT JOIN user_group ug ON cgr.group_id = ug.id",
            "<where>",
            "  <if test='keyword != null and keyword != \"\"'>",
            "    AND (c.name LIKE CONCAT('%', #{keyword}, '%') OR c.phone_number LIKE CONCAT('%', #{keyword}, '%'))",
            "  </if>",
            // 修改点：新增对 groupid 的判断
            "  <if test='groupid != 0'>",
            "    AND ug.id = #{groupid}",
            "  </if>",
            "</where>",
            "</script>"
    })
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "userId", column = "userId"),
            @Result(property = "name", column = "name"),
            @Result(property = "phoneNumber", column = "phoneNumber"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "birthDate", column = "birthDate"),
            @Result(property = "email", column = "email"),
            @Result(property = "address", column = "address"),
            @Result(property = "createTime", column = "createTime"),
            @Result(property = "updateTime", column = "updateTime"),
            @Result(property = "usergroups", column = "usergroups"),
            @Result(property = "groupid", column = "groupid")
    })
    IPage<Contacts> selectContactsWithGroup(
            Page<Contacts> page,
            @Param("keyword") String keyword,
            @Param("phoneNumber") String phoneNumber,
            @Param("groupid") int groupid
    );


    // 新增：拼音搜索方法（修正版）
    @Select({
            "<script>",
            "SELECT",
            "c.id,",
            "c.user_id AS userId,",
            "c.name,",
            "c.phone_number AS phoneNumber,",
            "c.gender,",
            "c.birth_date AS birthDate,",
            "c.email,",
            "c.address,",
            "c.create_time AS createTime,",
            "c.update_time AS updateTime,",
            "ug.group_name AS usergroups",
            "FROM contacts c",
            "JOIN (",
            "  SELECT ui.id",
            "  FROM user_info ui",
            "  WHERE ui.phone_number = #{phoneNumber}",
            ") ui ON c.user_id = ui.id",
            "LEFT JOIN contact_group_relation cgr ON c.id = cgr.contact_id",
            "LEFT JOIN user_group ug ON cgr.group_id = ug.id",
            "<where>",
            "  <if test='groupid != 0'>",
            "    AND ug.id = #{groupid}",
            "  </if>",
            "  <choose>",
            "    <when test='pinyinKeyword != null and pinyinKeyword != \"\"'>",
            "      AND (",
            "        c.phone_number LIKE CONCAT('%', #{pinyinKeyword}, '%')", // 手机号直接匹配
            "        OR ${pinyinFunction} LIKE CONCAT('%', #{pinyinKeyword}, '%')", // 全拼匹配（删除冗余的(c.name)）
            "        OR ${initialsFunction} LIKE CONCAT('%', #{initialsKeyword}, '%')", // 声母匹配（删除冗余的(c.name)）
            "      )",
            "    </when>",
            "  </choose>",
            "</where>",
            "</script>"
    })
    @Results({
            @Result(property = "id", column = "id", id = true),
            @Result(property = "userId", column = "userId"),
            @Result(property = "name", column = "name"),
            @Result(property = "phoneNumber", column = "phoneNumber"),
            @Result(property = "gender", column = "gender"),
            @Result(property = "birthDate", column = "birthDate"),
            @Result(property = "email", column = "email"),
            @Result(property = "address", column = "address"),
            @Result(property = "createTime", column = "createTime"),
            @Result(property = "updateTime", column = "updateTime"),
            @Result(property = "usergroups", column = "usergroups"),
            @Result(property = "groupid", column = "groupid")
    })
    IPage<Contacts> selectContactsWithPinyin(
            Page<Contacts> page,
            @Param("pinyinKeyword") String pinyinKeyword,
            @Param("initialsKeyword") String initialsKeyword,
            @Param("phoneNumber") String phoneNumber,
            @Param("groupid") int groupid,
            @Param("pinyinFunction") String pinyinFunction, // 例如："CONVERT(c.name USING gbk)"
            @Param("initialsFunction") String initialsFunction // 例如："SUBSTRING(CONVERT(c.name USING gbk), 1, 1)"
    );

    /**
     * 根据手机号和 userId 查询联系人 ID
     * @param phoneNumber 手机号
     * @param userId 用户 ID
     * @return 联系人 ID
     */
    @Select("SELECT id FROM contacts WHERE phone_number = #{phoneNumber} AND user_id = #{userId}")
    Integer findContactIdByPhoneNumberAndUserId(@Param("phoneNumber") String phoneNumber, @Param("userId") Integer userId);

    /**
     * 根据联系人 ID 删除联系人 - 分组关联记录
     * @param contactId 联系人 ID
     * @return 删除的记录数
     */
    @Delete("DELETE FROM contact_group_relation WHERE contact_id = #{contactId}")
    int deleteContactGroupRelationByContactId(@Param("contactId") Integer contactId);

    /**
     * 根据联系人 ID 删除联系人记录
     * @param contactId 联系人 ID
     * @return 删除的记录数
     */
    @Delete("DELETE FROM contacts WHERE id = #{contactId}")
    int deleteContactByContactId(@Param("contactId") Integer contactId);
    /**
     * 根据手机号码和用户 ID 判断联系人是否存在
     * @param phoneNumber 手机号码
     * @param userId 用户 ID
     * @return 如果存在返回 true，不存在返回 false
     */
    @Select("SELECT EXISTS(SELECT 1 FROM contacts WHERE phone_number = #{phoneNumber} AND user_id = #{userId})")
    boolean isexists(String phoneNumber, int userId);

    @Update("UPDATE contacts c " +
            "JOIN user_group g ON c.groupid = g.id " +
            "SET c.groupid = #{newGroupId}, c.usergroups = g.group_name " +
            "WHERE c.id = #{contactId} AND g.id = #{newGroupId}")
    void updateContactGroup(Integer contactId, Integer newGroupId);

    @Update("UPDATE contacts SET groupid = 0 WHERE groupid = #{groupId}")
    int updateGroupIdToZero(int groupId);

    @Insert("INSERT INTO contacts (user_id, name, phone_number, gender, birth_date, email, address, usergroups, groupid) " +
            "VALUES (#{userId}, #{name}, #{phoneNumber}, #{gender}, #{birthDate}, #{email}, #{address}, #{usergroups}, #{groupid})")
    int insertContact(Contacts contact);


    // ContactMapper.java
    @Select({
            "<script>",
            "SELECT",
            "c.id,",
            "c.user_id AS userId,",
            "c.name,",
            "c.phone_number AS phoneNumber,",
            "c.gender,",
            "c.birth_date AS birthDate,",
            "c.email,",
            "c.address,",
            "c.create_time AS createTime,",
            "c.update_time AS updateTime,",
            "ug.group_name AS usergroups",
            "FROM contacts c",
            "JOIN (",
            "  SELECT ui.id",
            "  FROM user_info ui",
            "  WHERE ui.phone_number = #{phoneNumber}",
            ") ui ON c.user_id = ui.id",
            "LEFT JOIN contact_group_relation cgr ON c.id = cgr.contact_id",
            "LEFT JOIN user_group ug ON cgr.group_id = ug.id",
            "<where>",
            "  <if test='groupid != 0'>",
            "    AND ug.id = #{groupid}",
            "  </if>",
            "</where>",
            "</script>"
    })
    @Results({ /* 结果映射保持不变 */ })
    IPage<Contacts> selectAllContacts(
            Page<Contacts> page,
            @Param("phoneNumber") String phoneNumber,
            @Param("groupid") int groupid
    );

}